package com.wcansoft.baseWeb.mybatis_plugin.dao;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.context.ApplicationContext;
import org.swift.util.lang.StringUtil;

import com.wcansoft.baseWeb.mybatis_plugin.orm.anotation.Column;
import com.wcansoft.baseWeb.mybatis_plugin.orm.anotation.Table;

public class MybatisSqlProvider{
	ApplicationContext context;
	
	/**
	 * entity中所有的字段信息，构造时确定
	 */
	protected HashMap<String, ColumnMeta> columnList = new HashMap<String, ColumnMeta>();
	/**
	 * 表中所有的字段信息，构造时确定,其中String为对应数据库中的字段的小写名称
	 */
	protected HashMap<String, ColumnMeta> fieldList = null;

	/**
	 * 本DAO对象对应的实体类
	 */
	protected Class<?> table_clazz;
	/**
	 * 本DAO对象对应的表名
	 */
	protected String table_name;
	/**
	 * 本DAO对象对应的主键信息
	 */
	protected ColumnMeta identity;
	/**
	 * 主键是否自增
	 */
	protected Boolean if_auto_increment = true;
	
	public String listPage(Map<String,Object> params)
	{
		final String query = query2sql(params, "query").toLowerCase();
		int currpage = (Integer)params.get("currpage");
		int pagesize = (Integer)params.get("pagesize");
		if(query.indexOf("select")==0)
		{
			return query +" limit "+currpage*pagesize+","+pagesize;
		}
		Class<?> clazz = (Class<?>) params.get("clazz");
		init(clazz, false);
		String sql = new SQL(){{
		      SELECT("*");
		      FROM(table_name);
		       WHERE(query);
		    }}.toString();
		    
		return sql+" limit "+currpage*pagesize+","+pagesize;
	}
	
	public String listQuery(final Map<String,Object> params)
	{
		//FIXME 在反射调用的过程中，是否能知道最外层方法的返回值类型，这样就可以省略该步骤直接拿到正真的返回值
		//或者在初始化的过程中进行init
		String sqlQuery = query2sql(params,"query").toLowerCase().trim();
		if(sqlQuery.indexOf("select")==0)
		{
			return sqlQuery;
		}
		Class<?> clazz =(Class<?>) params.get("clazz");
		init(clazz, true);
		String sql = new SQL(){{
		      SELECT("*");
		      FROM(table_name);
		       WHERE(query2sql(params,"query"));
		    }}.toString();
		    return sql;
	}
	
	public String getOne(final Map<String,Object> params)
	{
		String sqlQuery = query2sql(params,"query").toLowerCase().trim();
		if(sqlQuery.indexOf("select")==0)
		{
			return sqlQuery +" limit 0,1";
		}
		Class<?> clazz =(Class<?>) params.get("clazz");
		init(clazz, true);
		
		String sql = new SQL(){{
		      SELECT("*");
		      FROM(table_name);
		       WHERE(query2sql(params,"query"));
		    }}.toString()+" limit 0,1";
		    return sql;
	}
	
	public String getRowCount(Map<String,Object> params)
	{
		final String sqlQuery = query2sql(params,"query").toLowerCase().trim();
		if(sqlQuery.indexOf("select")==0)
		{
			return sqlQuery;
		}
		Class<?> clazz =(Class<?>) params.get("clazz");
		init(clazz, true);
		String sql = new SQL(){{
		      SELECT("count(1)");
		      FROM(table_name);
		       WHERE(sqlQuery);	       
		    }}.toString();
		return sql;
	}
	
	public String getOneRowCol(Map<String,Object> params)
	{
		Class<?> clazz =(Class<?>) params.get("clazz");
		init(clazz, true);
		String sqlQuery = query2sql(params,"query");
		return sqlQuery;
	}

	
	
	public String list(final Object object)
	{
		init(object.getClass(), true);
		String sql = new SQL(){{
		      SELECT("*");
		      FROM(table_name);
		       WHERE(fields2sql(object,"", "and",false));
		    }}.toString();
		    return sql;
	}

	
	//主键获得一条记录
	public String get(Map<String,Object> params) {
		Class<?> clazz = (Class<?>) params.get("clazz");
		init(clazz, false);
		SQL sql = new SQL(){{
			SELECT("*");
			FROM(table_name);
			WHERE(identity.getColumn_name()+" =#{id}");
		}};
        return sql.toString();  
	}
	
	//主键更新所有赋值字段
	public String update(Object object)
	{
		init(object.getClass(),true);
		final String set = fields2sql(object,"", ",",true);
		SQL sql = new SQL(){{
			UPDATE(table_name);
			SET(set);
			WHERE(identity.getColumn_name()+" =#{"+identity.getColumn_name()+"}");
		}};
        return sql.toString();  
	}
	
	//主键更新所有赋值字段
	public String save(Object object) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, SQLException
	{
		init(object.getClass(),true);
		Object obj = identityValue(object,identity.getField_name());
		if(obj==null)
		{
			return insert(object);
		}
		else{
			return update(object);
		}
	}
	
	public String updateQuery(Map<String,Object> params)
	{
		final String query = query2sql(params,"query").toLowerCase().trim();
		if(query.indexOf("update")==0)
		{
			return query;
		}
		Object object = params.get("object");
		init(object.getClass(),true);
		final	String set = fields2sql(object,"object.", ",",true);
		SQL sql = new SQL(){{
			UPDATE(table_name);
			SET(set);
			WHERE(query);
		}};
        return sql.toString();  

	}
	
	
	public String insertQuery(Map<String,Object> params)
	{
		String query = query2sql(params,"query").toLowerCase().trim();
		if(query.indexOf("insert")==0)
		{
			return query;
		}
        return null;  
	}

	
	
	public String delete(final Object object)
	{
		Class<? extends Object> clazz = object.getClass();
		init(clazz, true);
		
		return new SQL(){{ 
			DELETE_FROM(table_name);
			WHERE(fields2sql(object,"", "and",false));
			}}.toString();
	}
	
	public String deleteQuery(Map<String,Object> params)
	{
		Class<?> clazz = (Class<?>) params.get("clazz");
		final	String query = query2sql(params, "query").toLowerCase().trim();
		if(query.indexOf("delete")==0)
		{
			return query;
		}
		init(clazz,false);
		return new SQL(){{ 
			DELETE_FROM(table_name);
			WHERE(query);
			}}.toString();
	}
	
	public String insert(Object object) throws NoSuchMethodException, SecurityException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException, SQLException {
		StringBuffer sql = new StringBuffer();
		Class<?> clazz = object.getClass();
		init(clazz, true);
		List<String> list = getTableMeta(table_name);
		StringBuffer colBuf = new StringBuffer();
		StringBuffer parasBuf = new StringBuffer();

		for (String colName : list) {
			if (colName.equalsIgnoreCase(identity.getColumn_name()) && if_auto_increment)
				continue;
			ColumnMeta meta = this.columnList.get(colName.toLowerCase());
			if (meta == null)
				meta = this.columnList.get(underline4camel(colName,"_",""));
			if (meta == null)
				continue;

			// 字段是否为null
			String field = meta.getField_name();
			int fType = FieldType.getFieldType(meta.getColumn_clazz());
			String filedName = ((fType == FieldType.BOOLEAN) ? "is" : "get") + field.substring(0, 1).toUpperCase()
					+ field.substring(1, field.length());
			Method method = clazz.getDeclaredMethod(filedName, null);
			Object retObj = method.invoke(object, null);
			if (retObj == null)
				continue;

			colBuf.append(colName).append(",");
			parasBuf.append("#{" + meta.getField_name() + "},");
		}
		colBuf.deleteCharAt(colBuf.length() - 1);
		parasBuf.deleteCharAt(parasBuf.length() - 1);

		// 组装sql语句
		String insertOrReplace = "insert  into ";
		sql.append(insertOrReplace).append(table_name).append(" (").append(colBuf.toString());
		sql.append(") values (").append(parasBuf.toString()).append(")");
		return sql.toString();
	}

	//表里的字段
	List<String> getTableMeta(String table) throws SQLException {
		String sql = "select * from " + table + " limit 0,0";
		List<String> list = new ArrayList<String>();
		DataSource dataSource = DataSourceBeanUtil.getDataSource();
		Connection con = dataSource.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int columnCount = rsMetaData.getColumnCount();
			for (int i = 1; i <= columnCount; i++) {
				list.add(rsMetaData.getColumnName(i));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs.close();
			stmt.close();
			con.close();
		}
		return list;
	}
	
	//query转sql
	private String query2sql(Map<String,Object> params,String queryName)
	{
		Query query = (Query)params.get(queryName);
		Object values[] = query.getConditionValue().getVals();
		String condition = query.getConditionValue().getCondition();
		if(values!=null){
			for(int i=0;i<values.length;i++)
			{
				condition = condition.replaceFirst("\\?", "#{"+queryName+".vals["+i+"]}");
			}
		}
		
		return condition;
	}
	
	//对象转sql，例如"uid=#{uid},state=#{state}"，"uid=#{uid} and state>#{state}"
	private String fields2sql(Object object,String preValue,String split,Boolean ignoreid)
	{
		List<String> list = getFiledsHasValue(object);
		StringBuffer buf = new StringBuffer();
		for(String field:list)
		{
			if(!(ignoreid && field.equals(identity.getField_name())))
			{
				buf.append(" ").append(field).append("=#{").append(preValue).append(field).append("}").append(" ").append(split);
			}
		}
		return buf.delete(buf.length()-split.length(), buf.length()).toString();
	}
	
	//有值的字段
	private List<String> getFiledsHasValue(Object object)
	{
		Class<?> clazz = object.getClass();
		List<String> listFields = new ArrayList<String>();
		Set<String> list = columnList.keySet();
		for (String colName : list) {
			ColumnMeta meta = columnList.get(colName);
			// 字段是否为null
			String field = meta.getField_name();
			int fType = FieldType.getFieldType(meta.getColumn_clazz());
			String filedName = ((fType == FieldType.BOOLEAN) ? "is" : "get") + field.substring(0, 1).toUpperCase()
					+ field.substring(1, field.length());
			Object retObj=null;
			try{
				Method method = clazz.getDeclaredMethod(filedName, null);
				retObj = method.invoke(object, null);
			}
			catch(Exception e)
			{
				
			}
			if (retObj == null)
				continue;
			listFields.add(field);
		}
		return listFields;

	}
	
	//主键值
	private Object identityValue(Object object,String indetity_name)
	{
		String filedName = "get"+ indetity_name.substring(0, 1).toUpperCase()
				+ indetity_name.substring(1, indetity_name.length());
		Object retObj=null;
		try{
			Method method = object.getClass().getDeclaredMethod(filedName, null);
			retObj = method.invoke(object, null);
		}
		catch(Exception e)
		{
			
		}
		return retObj;
	}
	
	 private Field[] getFields(Class<?> beanClass) {  
	        Field[] beanFields = beanClass.getDeclaredFields();  
	        Class<?> beanSuperClass = beanClass.getSuperclass();  
	        Field[] beanSuperFields = beanSuperClass.getDeclaredFields();  
	        return ArrayUtils.addAll(beanFields, beanSuperFields);  
	    } 
	 
	 public String camel4underline(String param){  
	        Pattern  p=Pattern.compile("[A-Z]");  
	        if(param==null ||param.equals("")){  
	            return "";  
	        }  
	        StringBuilder builder=new StringBuilder(param);  
	        Matcher mc=p.matcher(param);  
	        int i=0;  
	        while(mc.find()){  
	            builder.replace(mc.start()+i, mc.end()+i, "_"+mc.group().toLowerCase());  
	            i++;  
	        }  
	  
	        if('_' == builder.charAt(0)){  
	            builder.deleteCharAt(0);  
	        }  
	        return builder.toString();  
	    }
	 
	 public String underline4camel(String srcStr,String org,String ob)  
	 {  
	    String newString = "";  
	    int first=0;  
	    while(srcStr.indexOf(org)!=-1)  
	    {  
	     first=srcStr.indexOf(org);  
	     if(first!=srcStr.length())  
	     {  
	      newString=newString+srcStr.substring(0,first)+ob;  
	      srcStr=srcStr.substring(first+org.length(),srcStr.length());  
	      srcStr=srcStr.substring(0, 1).toUpperCase() + srcStr.substring(1);  
	     }  
	    }  
	    newString=newString+srcStr;  
	    return newString;  
	 } 
	 
	void init(Class<?> clazz, boolean initField) {
		table_clazz = clazz;
		if (clazz != null) {
			// 默认使用类名作为表名
			String class_name = clazz.getName();
			table_name = camel4underline(class_name.substring((class_name.lastIndexOf('.') == -1 ? 0 : class_name.lastIndexOf('.') + 1),
					class_name.length())).toLowerCase();
			identity = new ColumnMeta();
			identity.setColumn_clazz(Long.class);
			identity.setColumn_name("id");
			identity.setField_name("id");
			// 获得表的属性
			if (clazz.isAnnotationPresent(Table.class)) {
				Table formLabel = (Table) clazz.getAnnotation(Table.class);
				table_name = StringUtil.isEmpty(formLabel.name()) ? table_name : formLabel.name();
				if_auto_increment = formLabel.auto_increment();
				identity.setField_name(formLabel.id());
			}
			if (initField) {
				// 获得字段名和类型，默认使用类成员属性作为表字段名，如果在@Column中自定义过按照自定义取
				for (Field fieldClass : clazz.getDeclaredFields()) {
					if (FieldType.getFieldType(fieldClass.getType()) == 0)
						continue;
					ColumnMeta meta = new ColumnMeta();
					meta.setColumn_clazz(fieldClass.getType());
					meta.setColumn_name(fieldClass.getName());
					meta.setField_name(fieldClass.getName());
					if (fieldClass.isAnnotationPresent(Column.class)) {
						Column fieldLabel = fieldClass.getAnnotation(Column.class);
						String label = StringUtil.isEmpty(fieldLabel.col_name()) ? fieldClass.getName()
								: fieldLabel.col_name();
						meta.setColumn_name(label);
					}
					if (meta.getField_name().equalsIgnoreCase(identity.getField_name())) {

						identity = meta;
					}
					columnList.put(meta.getColumn_name(), meta);
				}
			}
		}
	}

}
